package 数据库操作;

import com.mysql.cj.jdbc.JdbcConnection;
import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;

/**
 * @作者:侯国威
 * @时间: 2020/4/14/15:04
 * @邮箱:1045077189@qq.com 使用PrepateStatement实现批量数插入
 * <p>
 * updata、delete本身就具有批量操作的效果。
 * 此时的批量的操作，主要指的是批量插入。使用PrepateStatement如何实现的批量的插入
 */
public class MoreDataInsert {

    //批量操作一
    @Test
    public void MoreDataInsert() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCPackage.getconnection();
            String sql = "insert into student.insert_more_data(id)values (?)";

            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i < 40000; i++) {
                if (i % 2 == 0) {
                    preparedStatement.setInt(1, i);
                    preparedStatement.execute();
                }

            }
            long end = System.currentTimeMillis();
            System.out.println("插入成功,总花费" + ((end - start) / 1000) + "秒");
        } catch (IOException e) {
            e.printStackTrace();
        }
        assert connection != null;
        connection.close();
        preparedStatement.close();

    }


    /**
     * @作者:侯国威
     * @时间：2020/4/14_16:44
     * @描述:批量操作方式三 1、addBatch() executeBatcg() clearBatch()
     * mysql 服务器默认是关闭的，需要一个参数，让它开启批量处理
     * ?rewriteBatchedStatements=true 写在配置文件中 url后面
     */
    @Test
    public void MoreDataInsert1() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCPackage.getconnection();
            connection.setAutoCommit(false);
            String sql = "insert into student.count(id)values (?)";

            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {

                preparedStatement.setInt(1, i);

                preparedStatement.addBatch();
                if ( i % 500 ==0){
                    //执行
                    preparedStatement.executeBatch();
                    // 清空
                    preparedStatement.clearBatch();
                }



            }
            connection.commit();
            long end = System.currentTimeMillis();
            System.out.println("插入成功,总花费" + ((end - start) / 1000) + "秒");
        } catch (IOException e) {
            e.printStackTrace();
        }
        assert connection != null;
        connection.close();
        preparedStatement.close();

    }

}
